﻿using chihuopig.IService;
using System;
using System.Collections.Generic;
using System.Text;
using chihuopig.DTO;
using chihuopig.Model;
using System.Threading.Tasks;
using SqlSugar;
using System.Linq;
namespace chihuopig.Service
{
    public class jurisdictionService : DBService, IAuthorization
    {
        public Task<int> delAll(long[] Id)
        {
            return Task.Factory.StartNew<int>(s => {
                string ls = string.Join(",", (long[])s);
                string sql = $"update  jurisdiction set IsDelete='1' where id in ({ls})";
                return
                Sqlclient.Ado
               .ExecuteCommand(sql);
            }, Id);
        }

        public Task<int> Deleteable(long Id)
        {
            return   Sqlclient
                    .Updateable<jurisdiction>()
                    .UpdateColumns(it => new jurisdiction() { IsDelete = true })
                    .Where(c => c.Id == Id)
                    .ExecuteCommandAsync();
        }

        public Task<jurisdiction> EditAsync(long Id)
        {
            return Sqlclient.Queryable<jurisdiction>().Where(s => s.Id == Id).FirstAsync();
        }

        public Task<RoleModelDTO> FindRoleModelDTO(long RoleId)
        {

            return Task.Factory.StartNew(a =>
            {
                long rolId = (long)a;
                StringBuilder sql = new StringBuilder();
                sql.Append($"SELECT r.Id,r.`Name` as RoleName,GROUP_CONCAT(rj.JurisdictionId) as authorizationId from Role r LEFT JOIN Role_Jurisdiction rj on rj.RoleId=r.Id WHERE r.Id='{RoleId}' ");
                return Sqlclient.Ado.SqlQuery<RoleModelDTO>(sql.ToString()).Single();
            }, RoleId);
        }

        public Task<jurisdictionModel> GetjurisdictionById(long Id)
        {
           return Sqlclient.Queryable<jurisdiction,AdminMenu>
                ((j,a)=>new object[] {JoinType.Left,j.MenuId==a.Id})
                .Where((j,a) => j.Id== Id).Select((j, a) =>new jurisdictionModel()
                {
                         AdminMenuName=a.Name,
                         jurisdictId=j.Id,
                         MenuId=j.MenuId,
                         jurisdictName=j.Name,
                         Page=j.Page,
                         Prerogative=j.Prerogative
                }).SingleAsync();
        }

        public Task<List<jurisdictionMenuDTo>> GetPage(int pageIndex, int PageSize)
        {
            return Sqlclient
                .Queryable<jurisdiction, AdminMenu>((j, a) => new object[] { JoinType.Left, j.MenuId == a.Id })
                .Where((j, a) => j.IsDelete == false && a.IsDelete == false)
                .OrderBy((j, a) => j.Addtime, SqlSugar.OrderByType.Desc)
                .Select((j, a) => new jurisdictionMenuDTo() { MenuId=a.Id, MenuName=a.Name, Page=j.Page, Name=j.Name, Prerogative=j.Prerogative, jurisdictionId=j.Id })
                .ToPageListAsync(pageIndex, PageSize);
        }

        public Task<double> GetPageTotal(int PageSize)
        {
            return Task.Factory.StartNew<double>(s =>
            {
                double size = Convert.ToDouble(s);
                double bnum = Sqlclient
                .Queryable<jurisdiction, AdminMenu>((j, a) => new object[] { JoinType.Left, j.MenuId == a.Id })
                .Where((j, a) => j.IsDelete == false && a.IsDelete == false)
                .OrderBy((j, a) => j.Addtime, SqlSugar.OrderByType.Desc).Count() / size;
                //Math.Ceiling(bnum);
                return Math.Ceiling(bnum);

            }, PageSize);
        }

        public Task<List<RoleModel>> GetTable()
        {
            return Task.Run(()=>
            {
                StringBuilder sql = new StringBuilder();
                sql.Append("SELECT a.id as MenuId, a.`Name` as MenuName, GROUP_CONCAT(j.`Name`,'-',j.Id,'-',j.Prerogative,'-',j.Page) as JurisAny ");
                sql.Append(" FROM AdminMenu a LEFT JOIN jurisdiction j on j.MenuId=a.Id GROUP BY a.`Name`");
                return Sqlclient.Ado.SqlQuery<RoleModel>(sql.ToString());
            });
               
        }

        public Task<long> Insertable(jurisdiction Model)
        {
            return Sqlclient.Insertable(Model).ExecuteReturnBigIdentityAsync();
        }

        public Task<int> RoleAdd(roleaddVefiyDTO model)
        {
            return Task.Factory.StartNew(a =>
            {
                roleaddVefiyDTO mo=(roleaddVefiyDTO)a;
                StringBuilder Role_Jurisdiction = new StringBuilder();
                if (model.RoleId>0)
                {

                    Sqlclient.Ado.ExecuteCommand($"update Role set Name='{mo.RoleName}' where id='{model.RoleId}' ");
                    Sqlclient.Ado.ExecuteCommand($"delete from Role_Jurisdiction where JurisdictionId not in ({string.Join(",", mo.jurisd)}) and RoleId='{mo.RoleId}'");
                    foreach (var item in mo.jurisd)
                    {
                        if (Sqlclient.Queryable<Role_Jurisdiction>().Where(s => s.RoleId == mo.RoleId && s.JurisdictionId == item).Single() == null)
                        {
                            Sqlclient.Ado.ExecuteCommand($"insert into Role_Jurisdiction(RoleId,JurisdictionId,Addtime) values ('{mo.RoleId}','{item}',now()) ");
                        }
                    }

                    return 1;
                }
                else
                {
                    string sql = $"insert into Role(Name,Addtime) values ('{mo.RoleName}',now()); select  @@IDENTITY; ";
                    long id= Sqlclient.Ado.SqlQuery<long>(sql).Single();
                    foreach (var item in mo.jurisd)
                    {
                        Role_Jurisdiction.Append($"insert into Role_Jurisdiction(RoleId,JurisdictionId,Addtime) values('{id}','{item}',now()); ");
                    }
                    return Sqlclient.Ado.ExecuteCommand(Role_Jurisdiction.ToString());
                }
                
               
            }, model);
        }

        public Task<int> Updateable(jurisdiction Model)
        {
            return Sqlclient.Updateable(Model).ExecuteCommandAsync();
        }

      
    }
}
